import db from './dbserver'

async function getUserJurisdictionQuery(params) {
    let res = await db.query(
        `SELECT
	Jurisdiction.Jurisdiction_name,
	Jurisdiction.Jurisdiction_path,
	Jurisdiction.Jurisdiction_type,
    Jurisdiction.parent_id,
    Jurisdiction.id,
    Jurisdiction.order,
	Jurisdiction.type
    FROM user_role_temp
    LEFT JOIN role_jurisdiction_temp ON user_role_temp.role_id = role_jurisdiction_temp.role_id
    LEFT JOIN Jurisdiction ON role_jurisdiction_temp.jurisdiction_id = Jurisdiction.id
    WHERE user_id =:id AND Jurisdiction.type=:type ORDER BY Jurisdiction.order`, {
            replacements: {
                id: params.id,
                type: params.type
            },
            type: db.QueryTypes.SELECT
        })
    //没有则count为0，所以不用错误判断，直接返回即可
    return res
}

async function getUserRoleQuery(params) {
    let res = await db.query(
        `SELECT role.role_name, role.id FROM role left JOIN user_role_temp ON role.id = user_role_temp.role_id
         where user_id =:id`, {
            replacements: {
                id: params.id
            },
            type: db.QueryTypes.SELECT
        })
    //没有则count为0，所以不用错误判断，直接返回即可
    return res
}

async function deviceGroupByDepartment(params) {
    let res = await db.query(
        `SELECT count(device.id) as num,device.department FROM device where company = :company GROUP BY department`, {
            replacements: {
                company: params.company
            },
            type: db.QueryTypes.SELECT
        })
    //没有则count为0，所以不用错误判断，直接返回即可
    return res
}



export {
    getUserJurisdictionQuery,
    getUserRoleQuery,
    deviceGroupByDepartment
}
